/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sv.com.intesal.clases;

import java.io.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import sv.com.intesal.conexion.cConexion;

/**
 *
 * @author ejerez
 */
public class ReportExcel {

    private String nombreHojaExcel = "new sheet";
    private String fileName = "c:/data01.xls";

    private ResultSet getResultSet_from_SQL(String sql) throws Exception {
        try {
            return cConexion.conectar().prepareStatement(sql).executeQuery();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    public void excel_(String sql) throws Exception {

        try {
            ResultSet rs = this.getResultSet_from_SQL(sql);
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet(this.nombreHojaExcel);

            HSSFRow rowhead = sheet.createRow((short) 0);
            Integer columnCount = rs.getMetaData().getColumnCount();

            String columnType[] = new String[columnCount + 1];
            for (int i = 1; i <= columnCount; i++) {
                rowhead.createCell((short) i).setCellValue(rs.getMetaData().getColumnName(i));
                columnType[i] = rs.getMetaData().getColumnTypeName(i);
            }

            int i = 1;
            while (rs.next()) {
                HSSFRow row = sheet.createRow((short) i);
                for (int j = 1; j <= columnCount; j++) {

                    if (columnType[j].equals("varchar")) {
                        row.createCell((short) j).setCellValue(rs.getString(rs.getMetaData().getColumnName(j)));
                    } else if (columnType[j].equals("int8")) {
                        row.createCell((short) j).setCellValue(rs.getInt(rs.getMetaData().getColumnName(j)));
                    } else if (columnType[j].equals("date")) {
                        row.createCell((short) j).setCellValue(rs.getString(rs.getMetaData().getColumnName(j)));
                        //row.createCell((short) j).setCellValue(rs.getDate(rs.getMetaData().getColumnName(j)));
                    } else if (columnType[j].equals("timestamp")) {
                        row.createCell((short) j).setCellValue(rs.getString(rs.getMetaData().getColumnName(j)));
                        //row.createCell((short) j).setCellValue(rs.getTimestamp(rs.getMetaData().getColumnName(j)));
                    } else if (columnType[j].equals("float8")) {
                        row.createCell((short) j).setCellValue(rs.getDouble(rs.getMetaData().getColumnName(j)));
                    }
                }
                i++;
            }

            FileOutputStream fileOut = new FileOutputStream(this.fileName);
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated!");
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    public void excel() {
        try {
            String filename = "c:/data.xls";
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet("new sheet");

            HSSFRow rowhead = sheet.createRow((short) 0);
            rowhead.createCell((short) 0).setCellValue("SNo");
            rowhead.createCell((short) 1).setCellValue("Name");
            rowhead.createCell((short) 2).setCellValue("Address");
            rowhead.createCell((short) 3).setCellValue("Apellido");
            rowhead.createCell((short) 4).setCellValue("DUI");

            ResultSet rs = cConexion.conectar().prepareCall("SELECT * FROM EMPLEADO").executeQuery();
            int i = 1;
            while (rs.next()) {
                HSSFRow row = sheet.createRow((short) i);
                row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("id_e")));
                row.createCell((short) 1).setCellValue(rs.getString("nombre_e"));
                row.createCell((short) 2).setCellValue(rs.getString("direccion_e"));
                row.createCell((short) 3).setCellValue(rs.getString("apellido_e"));
                row.createCell((short) 4).setCellValue(rs.getString("dui_e"));
                i++;
            }
            FileOutputStream fileOut = new FileOutputStream(filename);
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated!");

        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
}
